package carassius.DAL;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collection;

/**
 *
 * @author siebz0r
 */
public abstract class OrganismTable<T extends OrganismRow>
		extends Table<T, Integer>
{
	private final String SQL_DELETE = "DELETE FROM organism WHERE id = ?",
			SQL_ADD = "INSERT INTO organism VALUES( NULL , ? , ? , ? , ? , ? , ? , ? )";

	//<editor-fold defaultstate="collapsed" desc="Public Methods">
	//<editor-fold defaultstate="collapsed" desc="SQL delete">
	@Override
	public boolean delete(Integer id) throws SQLException
	{
		boolean result = false;
		if (id != null
				&& id >= 0)
		{
			Database.getOrganismListTable().deleteByOrganismID(id);
			Database.getOptimalOrganismWaterConditionTable().deleteByOrganismID(id);

			PreparedStatement st = Database.prepareStatement(SQL_DELETE);
			st.setInt(1, id);

			synchronized (getRows())
			{
				try
				{
					if (st.executeUpdate() == 1)
					{
						result = true;
						if (getRows() != null && !getRows().isEmpty())
						{
							getRows().remove(id);
						}
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return result;
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="SQL get">
	public abstract Collection<T> getByAquariumId(int id) throws SQLException;
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="SQL add">
	@Override
	public Integer add(Object[] args) throws SQLException
	{
		Integer out = null;

		if (args != null
				&& args.length >= 7
				&& (args[0] != null || args[1] != null))
		{
			PreparedStatement st = Database.prepareStatement(SQL_ADD);
			st.setObject(1, args[0], Types.VARCHAR); //scientific name
			st.setObject(2, args[1], Types.VARCHAR); //alternative name
			st.setObject(3, args[2], Types.VARCHAR); //description
			st.setObject(4, (args[3] instanceof Integer) ? args[3] : null, Types.INTEGER); //min temperature
			st.setObject(5, (args[4] instanceof Integer) ? args[4] : null, Types.INTEGER); //max temperature
			st.setObject(6, (args[5] instanceof Double) ? args[5] : null, Types.DOUBLE); //max size
			st.setObject(7, (args[6] instanceof PictureRow) ? ((PictureRow) args[6]).getId() : null, Types.INTEGER); //picture

			synchronized (getRows())
			{
				try
				{
					if (st.executeUpdate() == 1)
					{
						ResultSet keys = st.getGeneratedKeys();
						if (keys.next())
						{
							int id = keys.getInt(1);
							if (!keys.wasNull())
							{
								out = id;
							}
						}
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return out;
	}
	//</editor-fold>
	//</editor-fold>
}
